Result of Populated Tables(Staging,Dictionary and MergesFinalDic) 11

* The Zip File, contained the code to removed duplications in each  Dictionary Tables after it had been populated.

*In Step 4, the Staging table was populated, the result are as follow;


       


Using SSIS Toolbox, Drag the data flow task in the control flow dashboard for each dictionary table.





*Each Data Flow Task component is edit for Dic_BriefPerformanceOfMovies




  1.  click on Connection Manager. 
  2. select the connection manager that is needed
  3. Select the table that is needed. This table will be used for ALL the dictionary tables.
  4. Click on Columns and select all the Columns
  5. Click on Ok



*Code Expression for Derived Column

REPLACE(REPLACE(REPLACE(REPLACE(LifeTimeGross,"–",""),"$",""),",",""),"NULL","")



  1. In Data Conversion, the Data Types and Length will match the Destination table Data Types and Length




  1. Click on General and select the field as shown. The selected field will be the same for All Dictionary Tables
  2. Click on Connection, Select the connection manager and Source table that is needed. The Source table will be the same for All Dictionary tables
  3. Click on Columns , match the identical Column and select the columns that you need as shown.



  1. Click on Connection Manager, select the connection manager and the required destination table that is needed. The destination table will varies for each dictionary tables.
  2. Click on Mapping, match the available inputs columns to the available destinations columns.



*GIF below is to Edit Dic_MappingMovies


*GIF below is to Edit Dic_MoviesCollectionData

Derived Column Expression for Opening: REPLACE(REPLACE(REPLACE(REPLACE(Opening,"–",""),"$",""),",",""),"NULL","")
Derived Column Expression for Gross: REPLACE(REPLACE(REPLACE(REPLACE(Gross,"–",""),"$",""),",",""),"NULL","")
Derived Column 1 Expression  for Opening: ISNULL(Opening) || Opening == "" ? NULL(DT_CY) : (DT_CY)Opening
Derived Column 1 Expression for Gross: ISNULL(Gross) || Gross == "" ? NULL(DT_CY) : (DT_CY)Gross
Derived Column 1 Expression for ReleaseDate: (DT_STR,8000,1252)(TRIM(ReleaseDate) == "" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : ReleaseDate)




*GIF below is to Edit Dic_MoviesCrew



*GIF below is to Edit Dic_MoviesRating

Derived Column Expression for MetaScore: (DT_STR,8000,1252)(TRIM(MetaScore) == "NA" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : MetaScore)
Derived Column Expression for Revenue: (DT_STR,8000,1252)(TRIM(Revenue) == "NA" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : Revenue)



*GIF below is to Edit Dic_MoviesReleaseID



*GIF below is to Edit Dic_MoviesSummary

Derived Column Expression for Budget: REPLACE(REPLACE(REPLACE(REPLACE(Budget,"–",""),"$",""),",",""),"NULL","")
Derived Column 1 Expression for USDistributor: (DT_STR,8000,1252)(TRIM(USDistributor) == "" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : USDistributor)
Derived Column 1 Expression for Budget: (DT_STR,8000,1252)(TRIM(Budget) == "" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : Budget)
Derived Column 1 Expression for MPAA: (DT_STR,8000,1252)(TRIM(MPAA) == "" ? (DT_STR,8000,1252)NULL(DT_STR,8000,1252) : MPAA)



*GIF below is to edit Dic_MoviesTicketPrices



Populated Dictionary Tables







  1.  Using SSIS ToolBox, drag the Executed SQl Task and Data Flow Task in the Control Flow Dashboard
  2. In the Data Flow Task, OLE DB Source is used,used the Look up transformation to find the matching Column in each Dictionary Tables and OLE DB Destination is used



3. The GIF below, will Edit each component in the Executed SQL Task and Data Flow Task








OLE DB Source SSIS

Union All SSIS

Look Up Transformation SSIS

OLE DB Destination SSIS